/*
 Navicat Premium Data Transfer

 Source Server         : yjx
 Source Server Type    : MySQL
 Source Server Version : 80100 (8.1.0)
 Source Host           : localhost:3306
 Source Schema         : mes-demo

 Target Server Type    : MySQL
 Target Server Version : 80100 (8.1.0)
 File Encoding         : 65001

 Date: 12/12/2023 15:46:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `employee_id` bigint NOT NULL AUTO_INCREMENT COMMENT '员工id',
  `employee_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名\n',
  `Sex` int NOT NULL COMMENT '性别 男1 女0',
  `birthplace` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '籍贯\n',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '电话号码',
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '状态（在职、请假、离职。。。）',
  `hire_date` date DEFAULT NULL COMMENT '入职日期',
  `department` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门',
  `position` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '岗位',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '登陆密码',
  `permission_code` varchar(255) DEFAULT NULL COMMENT '权限码',
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of employee
-- ----------------------------
BEGIN;
INSERT INTO `employee` (`employee_id`, `employee_name`, `Sex`, `birthplace`, `phone`, `status`, `hire_date`, `department`, `position`, `birthday`, `password`, `permission_code`) VALUES (3, '钟中', 1, '广东阳江', '13889992222', '在岗', '2023-11-06', '车间', '锡膏装配员', '2023-11-06', '456', '');
INSERT INTO `employee` (`employee_id`, `employee_name`, `Sex`, `birthplace`, `phone`, `status`, `hire_date`, `department`, `position`, `birthday`, `password`, `permission_code`) VALUES (4, '李小梅', 0, '广东东莞', '13828283939', '在岗', '2023-11-02', '车间', '锡膏质检员', '2001-01-11', '123456', '');
INSERT INTO `employee` (`employee_id`, `employee_name`, `Sex`, `birthplace`, `phone`, `status`, `hire_date`, `department`, `position`, `birthday`, `password`, `permission_code`) VALUES (5, '王小艾', 1, '广东阳江', '13828283939', '在岗', '2023-11-02', '车间', '锡膏质检员', '2000-11-01', '123456', '');
COMMIT;

-- ----------------------------
-- Table structure for equipment
-- ----------------------------
DROP TABLE IF EXISTS `equipment`;
CREATE TABLE `equipment` (
  `equipment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '设备编码',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备名称',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备类型',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备状态',
  `plc_controller` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'plc通信设备标识码',
  `pruchase_date` date DEFAULT NULL COMMENT '采购日期',
  `description` varchar(255) DEFAULT NULL COMMENT '设备说明',
  PRIMARY KEY (`equipment_id`),
  UNIQUE KEY `plc_controller` (`plc_controller`) COMMENT 'plc控制是唯一的设备标识码'
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of equipment
-- ----------------------------
BEGIN;
INSERT INTO `equipment` (`equipment_id`, `name`, `type`, `status`, `plc_controller`, `pruchase_date`, `description`) VALUES (2, 'A031焊锡机', 'A', '停用设备', '192.168.111.11', '2023-11-03', '全自动焊锡');
INSERT INTO `equipment` (`equipment_id`, `name`, `type`, `status`, `plc_controller`, `pruchase_date`, `description`) VALUES (3, 'A021焊锡机', 'A', '在线', '192.168.111.12', NULL, '全自动焊锡');
INSERT INTO `equipment` (`equipment_id`, `name`, `type`, `status`, `plc_controller`, `pruchase_date`, `description`) VALUES (4, 'B029焊锡机', 'B', '在线', '192.168.111.13', NULL, '自动装配机');
INSERT INTO `equipment` (`equipment_id`, `name`, `type`, `status`, `plc_controller`, `pruchase_date`, `description`) VALUES (6, 'B021焊锡机', 'B', '在线', '192.168.111.15', NULL, '自动装配机');
COMMIT;

-- ----------------------------
-- Table structure for equipment_maintenance
-- ----------------------------
DROP TABLE IF EXISTS `equipment_maintenance`;
CREATE TABLE `equipment_maintenance` (
  `equipment_maintenance_id` bigint NOT NULL AUTO_INCREMENT COMMENT '保养表主键',
  `equipment_id` bigint DEFAULT NULL COMMENT '设备id',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '保养说明',
  `maintainer` varchar(255) DEFAULT NULL COMMENT '保养人',
  `maintenance_date` date DEFAULT NULL COMMENT '保养日期',
  `completion_date` date DEFAULT NULL COMMENT '保养结束日期',
  PRIMARY KEY (`equipment_maintenance_id`),
  KEY `equipment_id` (`equipment_id`),
  CONSTRAINT `equipment_maintenance_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`equipment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of equipment_maintenance
-- ----------------------------
BEGIN;
INSERT INTO `equipment_maintenance` (`equipment_maintenance_id`, `equipment_id`, `description`, `maintainer`, `maintenance_date`, `completion_date`) VALUES (1, 2, '定期检修', '黄珊珊', '2023-12-01', '2023-12-01');
INSERT INTO `equipment_maintenance` (`equipment_maintenance_id`, `equipment_id`, `description`, `maintainer`, `maintenance_date`, `completion_date`) VALUES (2, 2, '定期检修', '乔纳森乔鲁诺', '2024-01-01', '2024-01-01');
INSERT INTO `equipment_maintenance` (`equipment_maintenance_id`, `equipment_id`, `description`, `maintainer`, `maintenance_date`, `completion_date`) VALUES (3, 2, '定期检修', '乔纳森乔鲁诺', '2024-11-01', '2023-11-01');
COMMIT;

-- ----------------------------
-- Table structure for equipment_repair
-- ----------------------------
DROP TABLE IF EXISTS `equipment_repair`;
CREATE TABLE `equipment_repair` (
  `equipment_repair_id` bigint NOT NULL AUTO_INCREMENT COMMENT '维修表主键',
  `equipment_id` bigint DEFAULT NULL COMMENT '设备id',
  `fault_type` varchar(255) DEFAULT NULL COMMENT '故障类型',
  `fault_description` varchar(255) DEFAULT NULL COMMENT '故障描述',
  `repairer` varchar(255) DEFAULT NULL COMMENT '修理人',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',
  `repair_date` date DEFAULT NULL COMMENT '维修日期',
  `completion_date` date DEFAULT NULL COMMENT '完成日期',
  PRIMARY KEY (`equipment_repair_id`),
  KEY `equipment_id` (`equipment_id`),
  CONSTRAINT `equipment_repair_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`equipment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1731592666880245762 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of equipment_repair
-- ----------------------------
BEGIN;
INSERT INTO `equipment_repair` (`equipment_repair_id`, `equipment_id`, `fault_type`, `fault_description`, `repairer`, `remark`, `repair_date`, `completion_date`) VALUES (1, 2, '机械故障', '零件老化', '空条乔琳', '即使更换机油', '2023-12-05', '2023-12-06');
INSERT INTO `equipment_repair` (`equipment_repair_id`, `equipment_id`, `fault_type`, `fault_description`, `repairer`, `remark`, `repair_date`, `completion_date`) VALUES (2, 2, '机械故障', '电路板损坏', '空条乔琳', '', '2023-12-08', '2023-12-09');
COMMIT;

-- ----------------------------
-- Table structure for inspection_report
-- ----------------------------
DROP TABLE IF EXISTS `inspection_report`;
CREATE TABLE `inspection_report` (
  `inspection_report_id` bigint NOT NULL AUTO_INCREMENT COMMENT '质检报告id',
  `quality_inspection_plan_id` bigint DEFAULT NULL COMMENT '质检计划id',
  `quality_inspection_definition_id` bigint DEFAULT NULL COMMENT '质检定义id',
  `inspection_ratio` int DEFAULT NULL COMMENT '抽检比例（inspection_ratio）%',
  `exception_type` varchar(255) DEFAULT NULL COMMENT '异常类型',
  `exception_report` varchar(255) DEFAULT NULL COMMENT '异常报告',
  PRIMARY KEY (`inspection_report_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of inspection_report
-- ----------------------------
BEGIN;
INSERT INTO `inspection_report` (`inspection_report_id`, `quality_inspection_plan_id`, `quality_inspection_definition_id`, `inspection_ratio`, `exception_type`, `exception_report`) VALUES (1, 1, 1, 100, '物料异常', '锡膏过期');
COMMIT;

-- ----------------------------
-- Table structure for material
-- ----------------------------
DROP TABLE IF EXISTS `material`;
CREATE TABLE `material` (
  `material_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物料编号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '物料名称',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '物料类型',
  `description` varchar(255) DEFAULT NULL COMMENT '物料说明',
  `stock_quantity` int NOT NULL COMMENT '物料数量',
  `create_time` timestamp NOT NULL COMMENT '提取时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  `update_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '更新说明',
  PRIMARY KEY (`material_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of material
-- ----------------------------
BEGIN;
INSERT INTO `material` (`material_id`, `name`, `type`, `description`, `stock_quantity`, `create_time`, `update_time`, `update_description`) VALUES (2, '锡膏', 'A101类', '银色的锡膏', 500, '2023-11-08 10:57:20', '2023-11-08 10:57:20', NULL);
INSERT INTO `material` (`material_id`, `name`, `type`, `description`, `stock_quantity`, `create_time`, `update_time`, `update_description`) VALUES (3, '锡膏', 'A102类', '银色的锡膏', 500, '2023-11-08 10:57:31', '2023-11-08 10:57:31', NULL);
INSERT INTO `material` (`material_id`, `name`, `type`, `description`, `stock_quantity`, `create_time`, `update_time`, `update_description`) VALUES (4, '锡膏', 'A103类', '银色的锡膏', 500, '2023-11-08 10:57:37', '2023-11-08 10:57:37', NULL);
COMMIT;

-- ----------------------------
-- Table structure for order_bom
-- ----------------------------
DROP TABLE IF EXISTS `order_bom`;
CREATE TABLE `order_bom` (
  `order_bom_id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单bom表主键',
  `production_workstation_id` bigint DEFAULT NULL COMMENT '生产工位id',
  `material_consume_id` varchar(255) DEFAULT NULL COMMENT '用料表id—暂时用采购编码直接对应\n',
  PRIMARY KEY (`order_bom_id`),
  KEY `material_consume_id` (`material_consume_id`),
  KEY `production_workstation_id` (`production_workstation_id`),
  CONSTRAINT `order_bom_ibfk_5` FOREIGN KEY (`material_consume_id`) REFERENCES `wms-demo`.`material_detail_maintenance` (`purchase_code`),
  CONSTRAINT `order_bom_ibfk_6` FOREIGN KEY (`production_workstation_id`) REFERENCES `production_workstation` (`production_workstation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of order_bom
-- ----------------------------
BEGIN;
INSERT INTO `order_bom` (`order_bom_id`, `production_workstation_id`, `material_consume_id`) VALUES (1, 1, 'ABC123');
INSERT INTO `order_bom` (`order_bom_id`, `production_workstation_id`, `material_consume_id`) VALUES (2, 1, 'ABC124');
INSERT INTO `order_bom` (`order_bom_id`, `production_workstation_id`, `material_consume_id`) VALUES (3, 2, 'Abc125');
COMMIT;

-- ----------------------------
-- Table structure for pad_management
-- ----------------------------
DROP TABLE IF EXISTS `pad_management`;
CREATE TABLE `pad_management` (
  `pad_management_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'pad管理id',
  `workstation_id` bigint DEFAULT NULL COMMENT '所在工位id',
  `name` varchar(255) NOT NULL COMMENT 'pad名称',
  `ip` varchar(255) NOT NULL COMMENT 'pad的ip地址',
  `status` varchar(255) NOT NULL COMMENT '登录状态',
  `mac` varchar(255) NOT NULL COMMENT 'pad的mac地址',
  `printer_ip` varchar(255) NOT NULL COMMENT '打印机的ip地址',
  PRIMARY KEY (`pad_management_id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `mac` (`mac`),
  KEY `workstation_id` (`workstation_id`),
  CONSTRAINT `pad_management_ibfk_1` FOREIGN KEY (`workstation_id`) REFERENCES `workstation` (`workstation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of pad_management
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for process
-- ----------------------------
DROP TABLE IF EXISTS `process`;
CREATE TABLE `process` (
  `process_id` bigint NOT NULL AUTO_INCREMENT COMMENT '工序表主键id',
  `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '工序照片',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工序名称',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '工序描述',
  PRIMARY KEY (`process_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of process
-- ----------------------------
BEGIN;
INSERT INTO `process` (`process_id`, `image`, `name`, `description`) VALUES (1, '暂无', '工序B', '焊接主板');
INSERT INTO `process` (`process_id`, `image`, `name`, `description`) VALUES (2, '暂无', '工序A', '质检锡膏后，装配入锡膏机');
COMMIT;

-- ----------------------------
-- Table structure for process_route
-- ----------------------------
DROP TABLE IF EXISTS `process_route`;
CREATE TABLE `process_route` (
  `process_route_id` bigint NOT NULL AUTO_INCREMENT COMMENT '工艺路线id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工艺路线名',
  `description` varchar(255) DEFAULT NULL COMMENT '工艺路线详情',
  `process_list` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '该工艺的工序的集合',
  PRIMARY KEY (`process_route_id`),
  UNIQUE KEY `process_route_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of process_route
-- ----------------------------
BEGIN;
INSERT INTO `process_route` (`process_route_id`, `name`, `description`, `process_list`) VALUES (1, '工艺路线101', '工序A-->工序B', '[2,1]');
INSERT INTO `process_route` (`process_route_id`, `name`, `description`, `process_list`) VALUES (3, '工艺路线102', '工序A-->工序B', '[2,1]');
INSERT INTO `process_route` (`process_route_id`, `name`, `description`, `process_list`) VALUES (4, '工艺路线1', '工序A-->工序B', '[2,1]');
COMMIT;

-- ----------------------------
-- Table structure for process_workstation
-- ----------------------------
DROP TABLE IF EXISTS `process_workstation`;
CREATE TABLE `process_workstation` (
  `process_id` bigint DEFAULT NULL COMMENT '工序id',
  `workstation_type_id` bigint DEFAULT NULL COMMENT '工位类型id',
  `quantity` int DEFAULT NULL COMMENT '工位数量',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  KEY `process_id` (`process_id`),
  KEY `workstation_type_id` (`workstation_type_id`),
  CONSTRAINT `process_workstation_ibfk_1` FOREIGN KEY (`process_id`) REFERENCES `process` (`process_id`),
  CONSTRAINT `process_workstation_ibfk_2` FOREIGN KEY (`workstation_type_id`) REFERENCES `workstation_type` (`workstation_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of process_workstation
-- ----------------------------
BEGIN;
INSERT INTO `process_workstation` (`process_id`, `workstation_type_id`, `quantity`, `description`) VALUES (1, 1, 5, NULL);
INSERT INTO `process_workstation` (`process_id`, `workstation_type_id`, `quantity`, `description`) VALUES (1, 2, 3, NULL);
INSERT INTO `process_workstation` (`process_id`, `workstation_type_id`, `quantity`, `description`) VALUES (2, 3, 2, NULL);
INSERT INTO `process_workstation` (`process_id`, `workstation_type_id`, `quantity`, `description`) VALUES (2, 1, 1, NULL);
COMMIT;

-- ----------------------------
-- Table structure for product_definition
-- ----------------------------
DROP TABLE IF EXISTS `product_definition`;
CREATE TABLE `product_definition` (
  `product_definition_id` bigint NOT NULL AUTO_INCREMENT COMMENT '产品id',
  `product_model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '产品型号',
  `process_route_id` bigint DEFAULT NULL COMMENT '产品工艺路线（工艺表主键id）',
  `production_instruction` varchar(255) DEFAULT NULL COMMENT '生产指导',
  PRIMARY KEY (`product_definition_id`) USING BTREE,
  KEY `process_route_id` (`process_route_id`),
  CONSTRAINT `product_definition_ibfk_1` FOREIGN KEY (`process_route_id`) REFERENCES `process_route` (`process_route_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of product_definition
-- ----------------------------
BEGIN;
INSERT INTO `product_definition` (`product_definition_id`, `product_model`, `process_route_id`, `production_instruction`) VALUES (2, '101草莓主板', 1, '使用草莓形状的模具');
INSERT INTO `product_definition` (`product_definition_id`, `product_model`, `process_route_id`, `production_instruction`) VALUES (3, '101西瓜主板', 1, '（假装这里有生产指导）');
COMMIT;

-- ----------------------------
-- Table structure for product_process_bom
-- ----------------------------
DROP TABLE IF EXISTS `product_process_bom`;
CREATE TABLE `product_process_bom` (
  `product_process_bom_id` bigint NOT NULL AUTO_INCREMENT COMMENT '产品工序bom表主键\n',
  `product_definition_id` bigint DEFAULT NULL COMMENT '产品id',
  `process_id` bigint DEFAULT NULL COMMENT '工序id',
  `material_id` bigint DEFAULT NULL COMMENT '物料id',
  `quantity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '物料用量',
  `workstation_type_id` bigint DEFAULT NULL COMMENT '工位类型id',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用料描述',
  PRIMARY KEY (`product_process_bom_id`),
  KEY `process_id` (`process_id`),
  KEY `material_id` (`material_id`),
  KEY `workstation_type_id` (`workstation_type_id`),
  KEY `product_definition_id` (`product_definition_id`),
  CONSTRAINT `product_process_bom_ibfk_3` FOREIGN KEY (`material_id`) REFERENCES `material` (`material_id`),
  CONSTRAINT `product_process_bom_ibfk_4` FOREIGN KEY (`product_definition_id`) REFERENCES `product_definition` (`product_definition_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1729835653141737474 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of product_process_bom
-- ----------------------------
BEGIN;
INSERT INTO `product_process_bom` (`product_process_bom_id`, `product_definition_id`, `process_id`, `material_id`, `quantity`, `workstation_type_id`, `description`) VALUES (1, 2, 1, 3, '10', 1, '');
INSERT INTO `product_process_bom` (`product_process_bom_id`, `product_definition_id`, `process_id`, `material_id`, `quantity`, `workstation_type_id`, `description`) VALUES (2, 2, 1, 4, '10', 1, '');
INSERT INTO `product_process_bom` (`product_process_bom_id`, `product_definition_id`, `process_id`, `material_id`, `quantity`, `workstation_type_id`, `description`) VALUES (1729835653141737473, 3, 1, 4, '10', 1, '');
COMMIT;

-- ----------------------------
-- Table structure for production_arrange
-- ----------------------------
DROP TABLE IF EXISTS `production_arrange`;
CREATE TABLE `production_arrange` (
  `production_id` bigint NOT NULL AUTO_INCREMENT COMMENT '生产id',
  `delivery_date` date DEFAULT NULL COMMENT '交货日期',
  `process_route_id` bigint DEFAULT NULL COMMENT '生产工艺',
  `sales_order_id` bigint DEFAULT NULL COMMENT '订单id',
  PRIMARY KEY (`production_id`),
  KEY `process_route_id` (`process_route_id`),
  KEY `production_arrange_ibfk_2` (`sales_order_id`),
  CONSTRAINT `production_arrange_ibfk_1` FOREIGN KEY (`process_route_id`) REFERENCES `process_route` (`process_route_id`),
  CONSTRAINT `production_arrange_ibfk_2` FOREIGN KEY (`sales_order_id`) REFERENCES `sales_order` (`sales_order_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1728938068053970951 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of production_arrange
-- ----------------------------
BEGIN;
INSERT INTO `production_arrange` (`production_id`, `delivery_date`, `process_route_id`, `sales_order_id`) VALUES (2, '2023-12-02', 1, 5);
INSERT INTO `production_arrange` (`production_id`, `delivery_date`, `process_route_id`, `sales_order_id`) VALUES (3, '2023-12-01', 1, 5);
INSERT INTO `production_arrange` (`production_id`, `delivery_date`, `process_route_id`, `sales_order_id`) VALUES (4, '2023-12-01', 1, 5);
COMMIT;

-- ----------------------------
-- Table structure for production_workstation
-- ----------------------------
DROP TABLE IF EXISTS `production_workstation`;
CREATE TABLE `production_workstation` (
  `production_workstation_id` bigint NOT NULL AUTO_INCREMENT COMMENT '生产-工位对应表主键',
  `production_id` bigint NOT NULL COMMENT '生产编码',
  `process_id` bigint DEFAULT NULL COMMENT '工序id',
  `workstation_id` bigint DEFAULT NULL COMMENT '工位id',
  `description` varchar(255) DEFAULT NULL COMMENT '工位描述',
  PRIMARY KEY (`production_workstation_id`),
  KEY `process_id` (`process_id`),
  KEY `workstation_id` (`workstation_id`),
  KEY `production_id` (`production_id`),
  CONSTRAINT `production_workstation_ibfk_1` FOREIGN KEY (`process_id`) REFERENCES `process` (`process_id`),
  CONSTRAINT `production_workstation_ibfk_2` FOREIGN KEY (`workstation_id`) REFERENCES `workstation` (`workstation_id`),
  CONSTRAINT `production_workstation_ibfk_3` FOREIGN KEY (`production_id`) REFERENCES `production_arrange` (`production_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of production_workstation
-- ----------------------------
BEGIN;
INSERT INTO `production_workstation` (`production_workstation_id`, `production_id`, `process_id`, `workstation_id`, `description`) VALUES (1, 2, 2, 3, NULL);
INSERT INTO `production_workstation` (`production_workstation_id`, `production_id`, `process_id`, `workstation_id`, `description`) VALUES (2, 2, 2, 2, NULL);
COMMIT;

-- ----------------------------
-- Table structure for quality_inspection
-- ----------------------------
DROP TABLE IF EXISTS `quality_inspection`;
CREATE TABLE `quality_inspection` (
  `quality_inspection_definition_id` bigint NOT NULL AUTO_INCREMENT COMMENT '质检定义主键',
  `type` varchar(255) DEFAULT NULL COMMENT '质检类型（零件、成品）',
  `object_name` varchar(255) DEFAULT NULL COMMENT '质检对象名称',
  `object_id` bigint DEFAULT NULL COMMENT '质检对象id（产品定义id or 零件定义id）',
  `process_id` bigint DEFAULT NULL COMMENT '工序id',
  `workstation_type_id` bigint DEFAULT NULL COMMENT '工位类型id',
  `quality_characteristics` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '质量特性',
  `inspection_method` varchar(255) DEFAULT NULL COMMENT '检测方法',
  PRIMARY KEY (`quality_inspection_definition_id`),
  KEY `process_id` (`process_id`),
  KEY `workstation_type_id` (`workstation_type_id`),
  CONSTRAINT `quality_inspection_ibfk_1` FOREIGN KEY (`process_id`) REFERENCES `process` (`process_id`),
  CONSTRAINT `quality_inspection_ibfk_2` FOREIGN KEY (`workstation_type_id`) REFERENCES `workstation_type` (`workstation_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1730240761134411778 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of quality_inspection
-- ----------------------------
BEGIN;
INSERT INTO `quality_inspection` (`quality_inspection_definition_id`, `type`, `object_name`, `object_id`, `process_id`, `workstation_type_id`, `quality_characteristics`, `inspection_method`) VALUES (1, '物料检测', '101锡膏', 2, 2, 3, '锡膏呈现亮银色，检测机系数不小于0.8', '查看锡膏成色及检测机系数，颜色呈现灰色或检测机系数低于0.8即异常');
COMMIT;

-- ----------------------------
-- Table structure for quality_inspection_plan
-- ----------------------------
DROP TABLE IF EXISTS `quality_inspection_plan`;
CREATE TABLE `quality_inspection_plan` (
  `quality_inspection_plan_id` bigint NOT NULL AUTO_INCREMENT COMMENT '质检计划主键',
  `sales_order_id` bigint DEFAULT NULL COMMENT '订单id',
  `product_definition_id` bigint DEFAULT NULL COMMENT '产品id',
  `production_arrange_id` bigint DEFAULT NULL COMMENT '生产安排id',
  `inspection_date` date DEFAULT NULL COMMENT '质检日期',
  PRIMARY KEY (`quality_inspection_plan_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of quality_inspection_plan
-- ----------------------------
BEGIN;
INSERT INTO `quality_inspection_plan` (`quality_inspection_plan_id`, `sales_order_id`, `product_definition_id`, `production_arrange_id`, `inspection_date`) VALUES (1, 5, 2, 2, '2023-12-02');
COMMIT;

-- ----------------------------
-- Table structure for sales_order
-- ----------------------------
DROP TABLE IF EXISTS `sales_order`;
CREATE TABLE `sales_order` (
  `sales_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '销售订单主键',
  `product_definition_id` bigint DEFAULT NULL COMMENT '产品id',
  `quantity` varchar(255) DEFAULT NULL COMMENT '数量',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `finish_date` date DEFAULT NULL COMMENT '交货日期',
  PRIMARY KEY (`sales_order_id`),
  KEY `product_definition_id` (`product_definition_id`),
  CONSTRAINT `sales_order_ibfk_1` FOREIGN KEY (`product_definition_id`) REFERENCES `product_definition` (`product_definition_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of sales_order
-- ----------------------------
BEGIN;
INSERT INTO `sales_order` (`sales_order_id`, `product_definition_id`, `quantity`, `remark`, `finish_date`) VALUES (5, 2, '10', '无备注', '2023-12-01');
INSERT INTO `sales_order` (`sales_order_id`, `product_definition_id`, `quantity`, `remark`, `finish_date`) VALUES (6, 2, '10', '无备注', '2023-12-01');
INSERT INTO `sales_order` (`sales_order_id`, `product_definition_id`, `quantity`, `remark`, `finish_date`) VALUES (7, 2, '10', '无备注', '2023-12-01');
COMMIT;

-- ----------------------------
-- Table structure for workstation
-- ----------------------------
DROP TABLE IF EXISTS `workstation`;
CREATE TABLE `workstation` (
  `workstation_id` bigint NOT NULL AUTO_INCREMENT COMMENT '工位id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工位名称',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工位类型',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工位状态',
  `increase_date` date DEFAULT NULL COMMENT '创建日期',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '工位描述',
  `workstation_type_id` bigint DEFAULT NULL COMMENT '工位类型id',
  PRIMARY KEY (`workstation_id`),
  UNIQUE KEY `name` (`name`),
  KEY `workstation_type_id` (`workstation_type_id`),
  CONSTRAINT `workstation_ibfk_1` FOREIGN KEY (`workstation_type_id`) REFERENCES `workstation_type` (`workstation_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of workstation
-- ----------------------------
BEGIN;
INSERT INTO `workstation` (`workstation_id`, `name`, `type`, `status`, `increase_date`, `description`, `workstation_type_id`) VALUES (2, '工位2', '', '空闲', '2023-11-05', '锡膏质检', NULL);
INSERT INTO `workstation` (`workstation_id`, `name`, `type`, `status`, `increase_date`, `description`, `workstation_type_id`) VALUES (3, '工位3', '', '空闲', NULL, '锡膏装配', NULL);
COMMIT;

-- ----------------------------
-- Table structure for workstation_employee
-- ----------------------------
DROP TABLE IF EXISTS `workstation_employee`;
CREATE TABLE `workstation_employee` (
  `workstation_id` bigint NOT NULL,
  `employee_id` bigint NOT NULL,
  UNIQUE KEY `employee_id` (`employee_id`) USING BTREE,
  KEY `workstation_id` (`workstation_id`),
  CONSTRAINT `workstation_employee_ibfk_1` FOREIGN KEY (`workstation_id`) REFERENCES `workstation` (`workstation_id`),
  CONSTRAINT `workstation_employee_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of workstation_employee
-- ----------------------------
BEGIN;
INSERT INTO `workstation_employee` (`workstation_id`, `employee_id`) VALUES (2, 4);
INSERT INTO `workstation_employee` (`workstation_id`, `employee_id`) VALUES (2, 5);
INSERT INTO `workstation_employee` (`workstation_id`, `employee_id`) VALUES (3, 3);
COMMIT;

-- ----------------------------
-- Table structure for workstation_equipment
-- ----------------------------
DROP TABLE IF EXISTS `workstation_equipment`;
CREATE TABLE `workstation_equipment` (
  `workstation_id` bigint NOT NULL,
  `equipment_id` bigint NOT NULL,
  UNIQUE KEY `equipment_id` (`equipment_id`) USING BTREE,
  KEY `workstation_id` (`workstation_id`),
  CONSTRAINT `workstation_equipment_ibfk_1` FOREIGN KEY (`workstation_id`) REFERENCES `workstation` (`workstation_id`),
  CONSTRAINT `workstation_equipment_ibfk_2` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`equipment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of workstation_equipment
-- ----------------------------
BEGIN;
INSERT INTO `workstation_equipment` (`workstation_id`, `equipment_id`) VALUES (2, 2);
INSERT INTO `workstation_equipment` (`workstation_id`, `equipment_id`) VALUES (2, 3);
INSERT INTO `workstation_equipment` (`workstation_id`, `equipment_id`) VALUES (3, 4);
INSERT INTO `workstation_equipment` (`workstation_id`, `equipment_id`) VALUES (3, 6);
COMMIT;

-- ----------------------------
-- Table structure for workstation_type
-- ----------------------------
DROP TABLE IF EXISTS `workstation_type`;
CREATE TABLE `workstation_type` (
  `workstation_type_id` bigint NOT NULL AUTO_INCREMENT COMMENT '工位类型id',
  `workstation_type_name` varchar(255) DEFAULT NULL COMMENT '工位类型名称',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`workstation_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of workstation_type
-- ----------------------------
BEGIN;
INSERT INTO `workstation_type` (`workstation_type_id`, `workstation_type_name`, `description`) VALUES (1, '锡膏装配类', NULL);
INSERT INTO `workstation_type` (`workstation_type_id`, `workstation_type_name`, `description`) VALUES (2, '自动化设备放置类', NULL);
INSERT INTO `workstation_type` (`workstation_type_id`, `workstation_type_name`, `description`) VALUES (3, '锡膏质检类', '使用质检仪质检锡膏成分');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
